package dao;

import entity.Song;
import util.DatabaseUtil;
import java.sql.*;
import java.util.*;

public class SongDao {
    
    // 根据ID查询歌曲
    public Song findById(Long id) throws SQLException {
        String sql = "SELECT * FROM songs WHERE song_id = ?";
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setLong(1, id);
            ResultSet rs = stmt.executeQuery();
            if (rs.next()) {
                return mapResultSetToSong(rs);
            }
        }
        return null;
    }
    
    // 获取用户播放列表
    public List<Song> findPlaylistByUserId(Long userId) throws SQLException {
        List<Song> songs = new ArrayList<>();
        String sql = 
            "SELECT s.* FROM songs s " +
            "JOIN current_playlist cp ON s.song_id = cp.song_id " +
            "WHERE cp.user_id = ? " +
            "ORDER BY cp.position ASC";
            
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setLong(1, userId);
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                songs.add(mapResultSetToSong(rs));
            }
        }
        return songs;
    }
    
    // 获取热门音乐
    public List<Song> findHotSongs(int limit) throws SQLException {
        List<Song> songs = new ArrayList<>();
        String sql = "SELECT * FROM songs ORDER BY play_count DESC LIMIT ?";
        
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, limit);
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                songs.add(mapResultSetToSong(rs));
            }
        }
        return songs;
    }
    
    // 获取最近播放
    public List<Song> findRecentPlaysByUserId(Long userId) throws SQLException {
        List<Song> songs = new ArrayList<>();
        String sql = 
            "SELECT s.* FROM songs s " +
            "JOIN recent_plays rp ON s.song_id = rp.song_id " +
            "WHERE rp.user_id = ? " +
            "ORDER BY rp.position ASC " +
            "LIMIT 5";
            
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setLong(1, userId);
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                songs.add(mapResultSetToSong(rs));
            }
        }
        return songs;
    }
    
    // 添加到最近播放
    public void addToRecentPlays(Long userId, Long songId) throws SQLException {
        Connection conn = DatabaseUtil.getConnection();
        conn.setAutoCommit(false);
        try {
            // 1. 删除该歌曲的现有记录
            String deleteSql = "DELETE FROM recent_plays WHERE user_id = ? AND song_id = ?";
            try (PreparedStatement stmt = conn.prepareStatement(deleteSql)) {
                stmt.setLong(1, userId);
                stmt.setLong(2, songId);
                stmt.executeUpdate();
            }

            // 2. 删除position为5的记录
            String deleteLastSql = "DELETE FROM recent_plays WHERE user_id = ? AND position >= 5";
            try (PreparedStatement stmt = conn.prepareStatement(deleteLastSql)) {
                stmt.setLong(1, userId);
                stmt.executeUpdate();
            }

            // 3. 更新现有记录的position
            String updateSql = 
                "UPDATE recent_plays " +
                "SET position = position + 1 " +
                "WHERE user_id = ? " +
                "ORDER BY position DESC";
            try (PreparedStatement stmt = conn.prepareStatement(updateSql)) {
                stmt.setLong(1, userId);
                stmt.executeUpdate();
            }

            // 4. 插入新记录
            String insertSql = "INSERT INTO recent_plays (user_id, song_id, position) VALUES (?, ?, 1)";
            try (PreparedStatement stmt = conn.prepareStatement(insertSql)) {
                stmt.setLong(1, userId);
                stmt.setLong(2, songId);
                stmt.executeUpdate();
            }
            
            conn.commit();
        } catch (Exception e) {
            conn.rollback();
            throw e;
        } finally {
            conn.setAutoCommit(true);
        }
    }
    
    // 更新播放次数
    public void incrementPlayCount(Long songId) throws SQLException {
        String sql = "UPDATE songs SET play_count = play_count + 1 WHERE song_id = ?";
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setLong(1, songId);
            stmt.executeUpdate();
        }
    }
    
    // 分页查询
    public List<Song> findPage(int page, int pageSize) throws SQLException {
        List<Song> songs = new ArrayList<>();
        String sql = "SELECT * FROM songs ORDER BY song_id LIMIT ? OFFSET ?";
        
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, pageSize);
            stmt.setInt(2, (page - 1) * pageSize);
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                songs.add(mapResultSetToSong(rs));
            }
        }
        return songs;
    }
    
    // 获取总记录数
    public int count() throws SQLException {
        String sql = "SELECT COUNT(*) as total FROM songs";
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            ResultSet rs = stmt.executeQuery();
            if (rs.next()) {
                return rs.getInt("total");
            }
        }
        return 0;
    }
    
    // ResultSet映射到实体
    public Song mapResultSetToSong(ResultSet rs) throws SQLException {
        Song song = new Song();
        song.setSongId(rs.getLong("song_id"));
        song.setTitle(rs.getString("title"));
        song.setArtist(rs.getString("artist"));
        song.setMusicFilePath(rs.getString("music_file_path"));
        song.setCoverFilePath(rs.getString("cover_file_path"));
        song.setLyricFilePath(rs.getString("lyric_file_path"));
        song.setDuration(rs.getInt("duration"));
        song.setCreatorId(rs.getLong("creator_id"));
        song.setPlayCount(rs.getInt("play_count"));
        song.setCreatedAt(rs.getTimestamp("created_at"));
        return song;
    }
    
    // 创建歌曲
    public Song create(Song song) throws SQLException {
        String sql = "INSERT INTO songs (title, artist, music_file_path, cover_file_path, lyric_file_path, creator_id, duration) VALUES (?, ?, ?, ?, ?, ?, ?)";
        
        System.out.println("执行SQL: " + sql);
        System.out.println("参数:");
        System.out.println("title: " + song.getTitle());
        System.out.println("artist: " + song.getArtist());
        System.out.println("musicPath: " + song.getMusicFilePath());
        System.out.println("coverPath: " + song.getCoverFilePath());
        System.out.println("lyricPath: " + song.getLyricFilePath());
        System.out.println("creatorId: " + song.getCreatorId());
        System.out.println("duration: " + song.getDuration());
        
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
            stmt.setString(1, song.getTitle());
            stmt.setString(2, song.getArtist());
            stmt.setString(3, song.getMusicFilePath());
            stmt.setString(4, song.getCoverFilePath());
            stmt.setString(5, song.getLyricFilePath());
            stmt.setLong(6, song.getCreatorId());
            stmt.setInt(7, song.getDuration());
            
            int affected = stmt.executeUpdate();
            System.out.println("影响行数: " + affected);
            
            ResultSet rs = stmt.getGeneratedKeys();
            if (rs.next()) {
                song.setSongId(rs.getLong(1));
                System.out.println("生成的ID: " + song.getSongId());
            }
            return song;
        } catch (SQLException e) {
            System.err.println("数据库错误: " + e.getMessage());
            throw e;
        }
    }
    
    // 删除歌曲
    public void delete(Long songId) throws SQLException {
        Connection conn = DatabaseUtil.getConnection();
        conn.setAutoCommit(false);
        try {
            // 删除相关表中的记录
            String[] deleteSqls = {
                "DELETE FROM current_playlist WHERE song_id = ?",
                "DELETE FROM recent_plays WHERE song_id = ?", 
                "DELETE FROM playlist_songs WHERE song_id = ?",
                "DELETE FROM songs WHERE song_id = ?"
            };
            
            for (String sql : deleteSqls) {
                try (PreparedStatement stmt = conn.prepareStatement(sql)) {
                    stmt.setLong(1, songId);
                    stmt.executeUpdate();
                }
            }
            
            conn.commit();
        } catch (Exception e) {
            conn.rollback();
            throw e;
        } finally {
            conn.setAutoCommit(true);
        }
    }
    
    // 获取用户上传的歌曲
    public List<Song> findByCreatorId(Long userId) throws SQLException {
        List<Song> songs = new ArrayList<>();
        String sql = "SELECT * FROM songs WHERE creator_id = ? ORDER BY created_at DESC";
        
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setLong(1, userId);
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                songs.add(mapResultSetToSong(rs));
            }
        }
        return songs;
    }
    
    // 搜索歌曲
    public List<Song> searchSongs(String keyword) throws SQLException {
        List<Song> songs = new ArrayList<>();
        String sql = 
            "SELECT * FROM songs " +
            "WHERE LOWER(title) LIKE LOWER(?) OR LOWER(artist) LIKE LOWER(?) " +
            "ORDER BY play_count DESC " +
            "LIMIT 50";  // 限制返回数量
            
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            String searchPattern = "%" + keyword + "%";
            stmt.setString(1, searchPattern);
            stmt.setString(2, searchPattern);
            
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                songs.add(mapResultSetToSong(rs));
            }
        }
        return songs;
    }
    
    // 获取搜索建议
    public List<Map<String, String>> getSuggestions(String keyword) throws SQLException {
        List<Map<String, String>> suggestions = new ArrayList<>();
        String sql = 
            "SELECT DISTINCT title, artist, play_count " +
            "FROM songs " +
            "WHERE LOWER(title) LIKE LOWER(?) OR LOWER(artist) LIKE LOWER(?) " +
            "ORDER BY play_count DESC " +
            "LIMIT 10";
            
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            String searchPattern = "%" + keyword + "%";
            stmt.setString(1, searchPattern);
            stmt.setString(2, searchPattern);
            
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                Map<String, String> suggestion = new HashMap<>();
                suggestion.put("title", rs.getString("title"));
                suggestion.put("artist", rs.getString("artist"));
                suggestions.add(suggestion);
            }
        }
        return suggestions;
    }
    
    // 更新指定范围内歌曲的位置
    public void updateRecentPlaysPositions(Long userId, int startPos, int endPos, int offset) throws SQLException {
        String sql = "UPDATE recent_plays SET position = position + ? " +
                     "WHERE user_id = ? AND position BETWEEN ? AND ?";
                     
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, offset);
            stmt.setLong(2, userId);
            stmt.setInt(3, startPos);
            stmt.setInt(4, endPos);
            stmt.executeUpdate();
        }
    }

    // 更新指定歌曲的位置
    public void updateRecentPlayPosition(Long userId, Long songId, int newPosition) throws SQLException {
        String sql = "UPDATE recent_plays SET position = ? WHERE user_id = ? AND song_id = ?";
        
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, newPosition);
            stmt.setLong(2, userId);
            stmt.setLong(3, songId);
            stmt.executeUpdate();
        }
    }

    // 添加歌曲到最近播放并指定位置
    public void addToRecentPlaysWithPosition(Long userId, Long songId, int position) throws SQLException {
        String sql = "INSERT INTO recent_plays (user_id, song_id, position) VALUES (?, ?, ?)";
        
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setLong(1, userId);
            stmt.setLong(2, songId);
            stmt.setInt(3, position);
            stmt.executeUpdate();
        }
    }

    // 从最近播放中移除指定歌曲
    public void removeFromRecentPlays(Long userId, Long songId) throws SQLException {
        String sql = "DELETE FROM recent_plays WHERE user_id = ? AND song_id = ?";
        
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setLong(1, userId);
            stmt.setLong(2, songId);
            stmt.executeUpdate();
        }
    }
}